﻿using PersistentObject.Common;
using PersistentObject.Common.Criteria;
using PersistentObject.Common.Exceptions;
using PersistentObject.Common.Schema;
using PersistentObject.Common.Sorts;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;


namespace PersistentObject
{
    /// <summary>
    /// Represent a database connection which provides the database manipulations
    /// </summary>
    public class POConnection
    {
        /// <summary>
        /// Indicate the database connection string
        /// Readonly
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return Config == null ? null : Config.ConnectionString;
            }
        }

        /// <summary>
        /// Indicate the database dialect
        /// Readonly
        /// </summary>
        public BaseDialect Dialect
        {
            get
            {
                return Config == null ? null : Config.Dialect;
            }
        }

        public IDbConnection DbConnection { get; internal set; }

        public IDbTransaction DbTransaction { get; internal set; }

        public ConnectionConfig Config { get; internal set; }

        /// <summary>
        /// Constructive function which requires a database connection string and an instance of database dialect
        /// </summary>
        /// <param name="connectionString">Indicate the database connection string</param>
        /// <param name="dialect">Indicate the instance of the database dialect</param>
        public POConnection(string connectionString, BaseDialect dialect) 
        {
            if (dialect == null)
            {
                throw new LackOfDialectException("The parameter dialect should not be null value.");
            }
            Config = new ConnectionConfig(connectionString, dialect);
            DbConnection = Dialect.CreateConnection(ConnectionString);
        }

        public POConnection(ConnectionConfig config)
        {
            Config = config;
            DbConnection = Dialect.CreateConnection(ConnectionString);
        }

        #region transaction functions
        /// <summary>
        /// Begin a database transaction and store it to the Transaction property
        /// </summary>
        /// <returns>Return the created database transaction</returns>
        public void BeginTransaction()
        {
            bool wasClosed = DbConnection != null && DbConnection.State == ConnectionState.Closed;
            if (wasClosed) DbConnection.Open();
            DbTransaction = DbConnection.BeginTransaction();
        }

        /// <summary>
        /// Commit the current database transaction and eliminate the Transaction property
        /// </summary>
        /// <returns>Return the commited transaction</returns>
        public void CommitTransaction()
        {
            if (DbTransaction == null)
            {
                return;
            }
            else
            {
                DbTransaction.Commit();
                DbTransaction = null;
            }
        }

        /// <summary>
        /// Rollback the current database transaction and eliminate the Transaction property
        /// </summary>
        /// <returns>Return the rollback transaction</returns>
        public void RollbackTransaction()
        {
            if (DbTransaction == null)
            {
                return;
            }
            else
            {
                DbTransaction.Rollback();
                DbTransaction = null;
            }
        }
        #endregion

        public IEnumerable<T> Load<T>(BaseCriteria criteria, OrderBy[] orderBys, int pageSize, int pageIndex)
        {
            string sql = Dialect.CreateLoadSql<T>(criteria, orderBys, pageSize, pageIndex);
            IEnumerable<IDataParameter> parameters = Dialect.PrepareCriteriaParameters<T>(criteria);
            SqlExecutor executor = new SqlExecutor(this);
            var result = executor.Load<T>(sql, parameters);
            return result;
        }

        public IEnumerable<T> Load<T>(BaseCriteria criteria, int pageSize, int pageIndex)
        {
            return Load<T>(criteria, null, pageSize, pageIndex);
        }

        public IEnumerable<T> Load<T>(int pageSize, int pageIndex)
        {
            return Load<T>(null, null, pageSize, pageIndex);
        }

        public IEnumerable<T> Load<T>(BaseCriteria criteria, OrderBy[] orderBys)
        {
            string sql = Dialect.CreateLoadSql<T>(criteria, orderBys);
            IEnumerable<IDataParameter> parameters = Dialect.PrepareCriteriaParameters<T>(criteria);
            SqlExecutor executor = new SqlExecutor(this);
            var result = executor.Load<T>(sql, parameters);
            return result;
        }

        public IEnumerable<T> Load<T>(BaseCriteria criteria)
        {
            return Load<T>(criteria, null);
        }

        public IEnumerable<T> Load<T>()
        {
            return Load<T>(null, null);
        }

        public int Count<T>(BaseCriteria criteria)
        {
            string sql = Dialect.CreateCountSql<T>(criteria);
            IEnumerable<IDataParameter> parameters = Dialect.PrepareCriteriaParameters<T>(criteria);
            SqlExecutor executor = new SqlExecutor(this);
            var result = executor.Count<T>(sql, parameters);
            return result;

        }

        public int Count<T>()
        {
            return Count<T>(null);
        }

        public T Save<T>(T obj)
        {
            SqlExecutor executor = new SqlExecutor(this);

            string isExistingSql = Dialect.GetIsExistingSql<T>();
            var isExisting = executor.IsExisting<T>(isExistingSql, obj);
            if(isExisting)
            {
                var updateSql = Dialect.GetUpdateSql<T>();
                var result = executor.Update<T>(updateSql, obj);
                return result;
            }
            else
            {
                var insertSql = Dialect.GetInsertSql<T>();
                var result =   executor.Insert<T>(insertSql, obj);
                return result;
            }
        }

        public IEnumerable<T> Save<T>(IEnumerable<T> objects)
        {
            //need to use incriteria
            foreach(var obj in objects)
            {
                var result = Save<T>(obj);
            }
            return objects;
        }

        public void Delete<T>(BaseCriteria criteria)
        {
            string sql = Dialect.GetDeleteSql<T>(criteria);
            IEnumerable<IDataParameter> parameters = Dialect.PrepareCriteriaParameters<T>(criteria);
            SqlExecutor executor = new SqlExecutor(this);
            executor.Delete<T>(sql, parameters);
        }

        public T Delete<T>(T obj)
        {
            string sql = Dialect.GetDeleteSql<T>();
            SqlExecutor executor = new SqlExecutor(this);
            executor.Delete<T>(sql, obj);
            return obj;
        }

        public IEnumerable<T> Delete<T>(IEnumerable<T> objects)
        {
            //need to use InCriteria
            foreach(var obj in objects)
            {
                var result = Delete<T>(obj);
            }
            return objects;
        }

        #region New Functions
        public Loader<T> Select<T>()
        {
            return new Loader<T>(this);
        }
        public Saver<T> Save<T>()
        {
            return new Saver<T>(this);
        }
        public Deleter<T> Delete<T>()
        {
            return new Deleter<T>(this);
        }

        public class Loader<T>
        {
            public POConnection Connection { get; internal set; }
            public BaseCriteria Criteria { get; internal set; }
            public List<OrderBy> OrderByList { get; internal set; }
            public bool UsePagination { get; internal set; }
            public int Size { get; internal set; }
            public int Index { get; internal set; }
            public Loader(POConnection connection)
            {
                Connection = connection;
                OrderByList = new List<OrderBy>();
            }
            public Loader<T> Where(BaseCriteria criteria)
            {
                Criteria = criteria;
                return this;
            }
            public Loader<T> OrderBy(OrderBy orderby)
            {
                OrderByList.Add(orderby);
                return this;
            }
            public Loader<T> OrderBy(string propertyName)
            {
                return OrderBy(new OrderByProperty(propertyName));
            }
            public Loader<T> OrderBy(string propertyName, OrderByType type)
            {
                return OrderBy(new OrderByProperty(propertyName, type));
            }
            public Loader<T> PageSize(int size)
            {
                UsePagination = true;
                this.Size = size;
                return this;
            }
            public Loader<T> PageIndex(int index) //0-based
            {
                UsePagination = true;
                this.Index = index;
                return this;
            }
            public IEnumerable<T> Load()
            {
                if(UsePagination)
                {
                    return Connection.Load<T>(this.Criteria, this.OrderByList.ToArray(), this.Size, this.Index);
                }
                else
                {
                    return Connection.Load<T>(this.Criteria, this.OrderByList.ToArray());
                }
            }
            public int Count()
            {
                return Connection.Count<T>(this.Criteria);
            }
        }
        public class Saver<T>
        {
            public POConnection Connection { get; internal set; }
            public List<T> ObjectList { get; internal set; }
            public Saver(POConnection connection)
            {
                Connection = connection;
                ObjectList = new List<T>();
            }
            public Saver<T> Add(T obj)
            {
                ObjectList.Add(obj);
                return this;
            }
            public Saver<T> AddRange(IEnumerable<T> range)
            {
                ObjectList.AddRange(range);
                return this;
            }
            public void Save()
            {
                Connection.Save<T>(ObjectList);
            }
        }
        public class Deleter<T>
        {
            public POConnection Connection { get; internal set; }
            public List<T> ObjectList { get; internal set; }
            public List<BaseCriteria> CriteriaList { get; internal set; }
            public Deleter(POConnection connection)
            {
                Connection = connection;
                ObjectList = new List<T>();
                CriteriaList = new List<BaseCriteria>();
            }

            public Deleter<T> Add(T obj)
            {
                ObjectList.Add(obj);
                return this;
            }
            public Deleter<T> AddRange(IEnumerable<T> range)
            {
                ObjectList.AddRange(range);
                return this;
            }
            public Deleter<T> AddCriteria(BaseCriteria criteria)
            {
                CriteriaList.Add(criteria);
                return this;
            }
            public void Delete()
            {
                Connection.Delete<T>(ObjectList);
                foreach(var criteria in CriteriaList)
                {
                    Connection.Delete<T>(criteria);
                }
            }
        }
        #endregion
    }
}
